package Util;


import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.HorizontalAlignment;
import org.apache.poi.ss.usermodel.VerticalAlignment;
import org.apache.poi.ss.util.CellRangeAddress;

import java.util.List;
import java.util.Map;

public class TrialExportUtilNew {

    public static HSSFWorkbook getHSSFWorkbookZc(String sheetName, String[] titleValues, String[] titleSecondValues, String[] titleKeys, List<Map<String, Object>> list, HSSFWorkbook wb,int type) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，设置标题样式

        CellStyle  styleCenter = wb.createCellStyle();
        styleCenter.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;
        /*
        styleCenter.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        styleCenter.setFillForegroundColor(IndexedColors.GREEN.getIndex());   //背景颜色*/
        //设置单元格为文本格式
        HSSFDataFormat format = wb.createDataFormat();
        styleCenter.setDataFormat(format.getFormat("@"));

        // 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(30);
        sheet.addMergedRegion(new CellRangeAddress(
                0, //开始行
                0, //结束行
                0, //开始列
                titleSecondValues.length - 1//结束列
        ));
        HSSFFont font = wb.createFont();
        font.setBold(true);
        styleCenter.setFont(font);
        HSSFCell cellTop = row.createCell(0);
        cellTop.setCellValue(sheetName);
        cellTop.setCellStyle(styleCenter);


        //第四步 单元格内容样式
        CellStyle styleCenter1 = wb.createCellStyle();
        styleCenter1.setAlignment(HorizontalAlignment.CENTER);     //居中
        /*styleCenter1.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        styleCenter1.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        styleCenter1.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        styleCenter1.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框*/

        // 设置表头样式
        CellStyle styleCenter2 = wb.createCellStyle();
        styleCenter2.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter2.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;


        //创建标题
        HSSFRow row1 = sheet.createRow(1);
        if(type==1){
            CellRangeAddress region1= new CellRangeAddress(1, 2, 0, 0);
            CellRangeAddress region2= new CellRangeAddress(1, 2, 1, 1);
            CellRangeAddress region3= new CellRangeAddress(1, 2, 2, 2);
            CellRangeAddress region4= new CellRangeAddress(1, 2, titleSecondValues.length - 1, titleSecondValues.length - 1);
            CellRangeAddress region5= new CellRangeAddress(1, 1, 3, 8);
            CellRangeAddress region6= new CellRangeAddress(1, 1, 9, 14);
            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);
            sheet.addMergedRegion(region3);
            sheet.addMergedRegion(region4);
            sheet.addMergedRegion(region5);
            sheet.addMergedRegion(region6);
        }else if(type==2){
            CellRangeAddress region1= new CellRangeAddress(1, 2, 0, 0);
            CellRangeAddress region2= new CellRangeAddress(1, 1, 1, 2);
            CellRangeAddress region3= new CellRangeAddress(1, 1, 3, 4);
            CellRangeAddress region5= new CellRangeAddress(1, 2, 5, 5);
            CellRangeAddress region6= new CellRangeAddress(1, 2, 6, 6);
            sheet.addMergedRegion(region1);
            sheet.addMergedRegion(region2);
            sheet.addMergedRegion(region3);
            sheet.addMergedRegion(region5);
            sheet.addMergedRegion(region6);
        }

        row1.setHeightInPoints(30);
        for (int i = 0; i < titleValues.length; i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellValue(titleValues[i]);
            cell.setCellStyle(styleCenter2);
            sheet.setColumnWidth(i, 16* 256);      //设置宽度
        }

        //创建2级标题
        HSSFRow rowSecond = sheet.createRow(2);
        for (int i = 0; i < titleSecondValues.length; i++) {
            HSSFCell cell = rowSecond.createCell(i);
            cell.setCellValue(titleSecondValues[i]);
            cell.setCellStyle(styleCenter2);
            if(i==0 || i== titleSecondValues.length-1){
                sheet.setColumnWidth(i, 18 * 256);      //设置宽度
            }else {
                sheet.setColumnWidth(i, 12 * 256);      //设置宽度
            }
        }
        rowSecond.setHeightInPoints(30);

        if (null != list && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {//赋值
                row = sheet.createRow(i + 3);
                Map<String, Object> map = list.get(i);
                for (int j = 0; j < titleKeys.length; j++) {
                    HSSFCell cell = row.createCell(j);   //设置单元格的数据类型
                    cell.setCellValue(map.get(titleKeys[j]) == null ? "" : map.get(titleKeys[j]).toString());
                    cell.setCellStyle(styleCenter1);
                }
            }
        }
        return wb;
    }


    public static HSSFWorkbook getHSSFWorkbook(String sheetName, String[] titleValues, String[] titleSecondValues, String[] titleKeys, List<Map<String, Object>> list, HSSFWorkbook wb) {
        // 第一步，创建一个HSSFWorkbook，对应一个Excel文件
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(sheetName);

        // 第三步，设置标题样式
        CellStyle  styleCenter = wb.createCellStyle();
        styleCenter.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;
   /*   styleCenter.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        styleCenter.setFillForegroundColor(IndexedColors.WHITE.getIndex());    //背景颜色
        styleCenter.setBorderLeft(BorderStyle.THIN);
        styleCenter.setBorderRight(BorderStyle.THIN);
        styleCenter.setBorderBottom(BorderStyle.THIN);
        styleCenter.setBorderTop(BorderStyle.THIN);*/
        //设置单元格为文本格式
        HSSFDataFormat format = wb.createDataFormat();
        styleCenter.setDataFormat(format.getFormat("@"));

        //创建字体样式
        //HSSFFont fontTitle = createFontStyle(wb, HSSFColor.BLACK.index, HSSFFont.BOLDWEIGHT_BOLD, 20);// 加粗
        //styleCenter.setFont(fontTitle);

        // 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(30);
        sheet.addMergedRegion(new CellRangeAddress(
                0, //开始行
                0, //结束行
                0, //开始列
                titleSecondValues.length - 1//结束列
        ));
        HSSFFont font = wb.createFont();
        font.setBold(true);
        styleCenter.setFont(font);
        HSSFCell cellTop = row.createCell(0);
        cellTop.setCellValue(sheetName);
        cellTop.setCellStyle(styleCenter);


        //第四步 单元格内容样式
        CellStyle styleCenter1 = wb.createCellStyle();
        styleCenter1.setAlignment(HorizontalAlignment.CENTER);     //居中


        // 设置表头样式
        CellStyle styleCenter2 = wb.createCellStyle();
        styleCenter2.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter2.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;
    /*  styleCenter2.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
        styleCenter2.setFillForegroundColor(HSSFColor.GREEN.index);    //背景颜色
        styleCenter2.setBorderBottom(HSSFCellStyle.BORDER_THIN); //下边框
        styleCenter2.setBorderLeft(HSSFCellStyle.BORDER_THIN);//左边框
        styleCenter2.setBorderTop(HSSFCellStyle.BORDER_THIN);//上边框
        styleCenter2.setBorderRight(HSSFCellStyle.BORDER_THIN);//右边框*/
        //创建字体样式
        //HSSFFont fontTitleTitle = createFontStyle(wb, HSSFColor.BLACK.index, HSSFFont.BOLDWEIGHT_NORMAL, 16);// 正常
        //styleCenter2.setFont(fontTitleTitle);

        //创建标题
        HSSFRow row1 = sheet.createRow(1);
        CellRangeAddress region0= new CellRangeAddress(1, 2, 0, 0);
        CellRangeAddress region1= new CellRangeAddress(1, 2, 1, 1);
        CellRangeAddress region2= new CellRangeAddress(1, 1, 2, 5);
        CellRangeAddress region3= new CellRangeAddress(1, 1, 6, titleSecondValues.length - 1);

        sheet.addMergedRegion(region0);
        sheet.addMergedRegion(region1);
        sheet.addMergedRegion(region2);
        sheet.addMergedRegion(region3);
        row1.setHeightInPoints(30);
        for (int i = 0; i < titleValues.length; i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellValue(titleValues[i]);
            cell.setCellStyle(styleCenter2);
            sheet.setColumnWidth(i, 15 * 256);      //设置宽度
        }

        //创建2级标题
        HSSFRow rowSecond = sheet.createRow(2);
        for (int i = 0; i < titleSecondValues.length; i++) {
            HSSFCell cell = rowSecond.createCell(i);
            cell.setCellValue(titleSecondValues[i]);
            cell.setCellStyle(styleCenter2);
            sheet.setColumnWidth(i, 16 * 256);      //设置宽度
        }
        rowSecond.setHeightInPoints(30);

        if (null != list && list.size() > 0) {
            for (int i = 0; i < list.size(); i++) {//赋值
                row = sheet.createRow(i + 3);
                Map<String, Object> map = list.get(i);
                for (int j = 0; j < titleKeys.length; j++) {
                    HSSFCell cell = row.createCell(j);   //设置单元格的数据类型
                    if (j == 0){
                        cell.setCellValue(i+1);
                    }else {
                        cell.setCellValue(map.get(titleKeys[j]) == null ? "" : map.get(titleKeys[j]).toString());
                    }
                    cell.setCellStyle(styleCenter1);
                }
            }
        }
        return wb;
    }

    public static HSSFWorkbook getHSSFWorkbookFxm(String title, String[] titleValues, String[] titleValuesSecond, String[] titleKeys, List<Map<String, Object>> resultlist, HSSFWorkbook wb, int type) {
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(title);

        // 第三步，设置标题样式
        CellStyle  styleCenter = wb.createCellStyle();
        styleCenter.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;
        //styleCenter.setFillPattern(FillPatternType.FINE_DOTS);
        //styleCenter.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());    //背景颜色


        //设置单元格为文本格式
        HSSFDataFormat format = wb.createDataFormat();
        styleCenter.setDataFormat(format.getFormat("@"));

        // 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(30);
        sheet.addMergedRegion(new CellRangeAddress(
                0, //开始行
                0, //结束行
                0, //开始列
                titleValuesSecond.length - 1//结束列
        ));

        HSSFFont font = wb.createFont();
        font.setBold(true);
        styleCenter.setFont(font);
        //设置粗体
        HSSFCell cellTop = row.createCell(0);
        cellTop.setCellValue(title);
        cellTop.setCellStyle(styleCenter);

        //第四步 单元格内容样式
        CellStyle styleCenter1 = wb.createCellStyle();
        styleCenter1.setAlignment(HorizontalAlignment.CENTER);     //居中

        // 设置表头样式
        CellStyle styleCenter2 = wb.createCellStyle();
        styleCenter2.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter2.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;

        //创建标题
        HSSFRow row1 = sheet.createRow(1);
        CellRangeAddress region1= new CellRangeAddress(1, 2, 0, 0);
        CellRangeAddress region2= new CellRangeAddress(1, 2, 1, 1);
        CellRangeAddress region3= new CellRangeAddress(1, 2, 2, 2);
        CellRangeAddress region4= new CellRangeAddress(1, 2, 3, 3);
        CellRangeAddress region5= new CellRangeAddress(1, 1, 4,titleValuesSecond.length-1 );

        sheet.addMergedRegion(region1);
        sheet.addMergedRegion(region2);
        sheet.addMergedRegion(region3);
        sheet.addMergedRegion(region4);
        sheet.addMergedRegion(region5);

        row1.setHeightInPoints(30);

        for (int i = 0; i < titleValues.length; i++) {
            HSSFCell cell = row1.createCell(i);
            cell.setCellValue(titleValues[i]);
            cell.setCellStyle(styleCenter2);
            sheet.setColumnWidth(i, 16 * 256);      //设置宽度
        }

        //创建2级标题
        HSSFRow rowSecond = sheet.createRow(2);
        for (int i = 0; i < titleValuesSecond.length; i++) {
            HSSFCell cell = rowSecond.createCell(i);
            cell.setCellValue(titleValuesSecond[i]);
            cell.setCellStyle(styleCenter2);
            sheet.setColumnWidth(i, 16 * 256);      //设置宽度
        }
        rowSecond.setHeightInPoints(30);

        if (null != resultlist && resultlist.size() > 0) {
            for (int i = 0; i < resultlist.size(); i++) {//赋值
                row = sheet.createRow(i + 3);
                Map<String, Object> map = resultlist.get(i);
                for (int j = 0; j < titleKeys.length; j++) {
                    HSSFCell cell = row.createCell(j);   //设置单元格的数据类型
                    if(j==0){
                        cell.setCellValue(i+1);
                    }else if(j>3){
                        String value=map.get(titleKeys[j]) == null ? "" : map.get(titleKeys[j]).toString() ;
                        String normal=map.get("normal") == null ? "" : map.get("normal").toString() ;
                        cell.setCellValue(("0".equals(value) || "0".equals(normal)) == true ? "是" : "否");
                    }else {
                        cell.setCellValue(map.get(titleKeys[j]) == null ? "" : map.get(titleKeys[j]).toString());
                    }
                    cell.setCellStyle(styleCenter1);
                }
            }
        }
        return wb;
    }



    public static HSSFWorkbook getHSSFWorkbookSgkf(String title, String[] titleValues, String[] titleValuesSecond, String[] titleKeys, List<Map<String, Object>> resultlist, HSSFWorkbook wb) {
        if (wb == null) {
            wb = new HSSFWorkbook();
        }

        // 第二步，在workbook中添加一个sheet,对应Excel文件中的sheet
        HSSFSheet sheet = wb.createSheet(title);

        // 第三步，设置标题样式
        CellStyle  styleCenter = wb.createCellStyle();
        styleCenter.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;
        //styleCenter.setFillPattern(FillPatternType.FINE_DOTS);
        //styleCenter.setFillForegroundColor(IndexedColors.GREY_80_PERCENT.getIndex());    //背景颜色


        //设置单元格为文本格式
        HSSFDataFormat format = wb.createDataFormat();
        styleCenter.setDataFormat(format.getFormat("@"));

        // 第四步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制
        HSSFRow row = sheet.createRow(0);
        row.setHeightInPoints(30);
        sheet.addMergedRegion(new CellRangeAddress(
                0, //开始行
                0, //结束行
                0, //开始列
                titleValuesSecond.length - 1//结束列
        ));

        HSSFFont font = wb.createFont();
        font.setBold(true);
        styleCenter.setFont(font);
        //设置粗体
        HSSFCell cellTop = row.createCell(0);
        cellTop.setCellValue(title);
        cellTop.setCellStyle(styleCenter);

        //第四步 单元格内容样式
        CellStyle styleCenter1 = wb.createCellStyle();
        styleCenter1.setAlignment(HorizontalAlignment.CENTER);     //居中

        // 设置表头样式
        CellStyle styleCenter2 = wb.createCellStyle();
        styleCenter2.setAlignment(HorizontalAlignment.CENTER);     //居中
        styleCenter2.setVerticalAlignment(VerticalAlignment.CENTER); //设置垂直对齐的样式为居中对齐;


        //创建2级标题
        HSSFRow rowSecond = sheet.createRow(1);
        for (int i = 0; i < titleValuesSecond.length; i++) {
            HSSFCell cell = rowSecond.createCell(i);
            cell.setCellValue(titleValuesSecond[i]);
            cell.setCellStyle(styleCenter2);
            sheet.setColumnWidth(i, 20*256);      //设置宽度
        }
        rowSecond.setHeightInPoints(30);

        if (null != resultlist && resultlist.size() > 0) {
            for (int i = 0; i < resultlist.size(); i++) {//赋值
                row = sheet.createRow(i + 2);
                Map<String, Object> map = resultlist.get(i);
                for (int j = 0; j < titleKeys.length; j++) {
                    HSSFCell cell = row.createCell(j);   //设置单元格的数据类型
                    if(j==0){
                        cell.setCellValue(i+1);
                    }else if(j>3){
                        String value=map.get(titleKeys[j]) == null ? "" : map.get(titleKeys[j]).toString() ;
                        String normal=map.get("normal") == null ? "" : map.get("normal").toString() ;
                        cell.setCellValue(("0".equals(value) || "0".equals(normal)) == true ? "是" : "否");
                    }else {
                        cell.setCellValue(map.get(titleKeys[j]) == null ? "" : map.get(titleKeys[j]).toString());
                    }
                    cell.setCellStyle(styleCenter1);
                }
            }
        }
        return wb;
    }

}

